package cn.green.core.components.dialect.impls.mysql.dialects;

import cn.green.core.common.QTableConstant;
import cn.green.core.components.dialect.impls.mysql.grammar.MysqlGrammars;
import cn.green.core.components.entity.DatabaseSql;
import cn.green.core.components.entity.JoinInfo;
import cn.green.core.components.exceptions.QDqlException;
import cn.green.core.components.field.FieldTypeCom;
import cn.green.core.components.setter.DeleteKey;
import cn.green.core.components.store.field.FieldTypeStore;
import cn.green.core.components.store.table.TableTypeStore;
import cn.green.core.components.table.TableTypeCom;
import cn.green.core.model.QCondition;
import com.alibaba.fastjson.JSONObject;
import cn.green.core.components.dialect.DatabaseDqlDialect;
import cn.green.core.model.QField;
import cn.green.core.model.QTable;

import java.util.*;

/**
 * @author wang.yishu
 * @version 1.0
 * @className MysqlDqlDialect
 * @description mysql数据库查询方言
 * @date 2024/11/19 11:13
 */
public class MysqlDqlDialect implements DatabaseDqlDialect {

    private void check(QTable table) {
        if (table == null) {
            throw new QDqlException("mysql dql language executes error : table structure is null.");
        }
        String tableName = table.getTableName();
        if (tableName == null || tableName.isEmpty()) {
            throw new QDqlException("mysql dql language executes error : table name is null.");
        }
    }

    /**
     * 建造select的语句
     * @param primaryTable 主表
     * @param joinInfos    链接表
     * @return
     */
    private String buildSelectColumnSql(QTable primaryTable, Map<String, JoinInfo> joinInfos) {
        StringBuilder sql = new StringBuilder();
        List<QField> primaryTableFields = primaryTable.getFields();
        if (primaryTableFields == null || primaryTableFields.isEmpty()) {
            throw new QDqlException("mysql dql language executes error : table fields is null.");
        }
        joinInfos = joinInfos == null? new HashMap<>() : joinInfos;
        boolean hasJoinInfos = !joinInfos.isEmpty();
        int primaryTableFieldIndex = 0;
        for (QField primaryTableField : primaryTableFields) {
            String fieldType = primaryTableField.getFieldType();
            if (fieldType != null && !fieldType.isEmpty()) {
                FieldTypeCom fieldTypeCom = FieldTypeStore.get(fieldType);
                if (fieldTypeCom != null && fieldTypeCom.isVirtual()) {
                    continue;
                }
            }
            sql.append(primaryTableField.getSelectColumnName());
            sql.append(MysqlGrammars.SYMBOL.BLANKET);
            sql.append(MysqlGrammars.DQL.AS);
            sql.append(MysqlGrammars.SYMBOL.BLANKET);
            sql.append(MysqlGrammars.SYMBOL.S_ET_MARK).append(primaryTableField.getSelectColumnName()).append(MysqlGrammars.SYMBOL.S_ET_MARK);
            if (primaryTableFieldIndex < primaryTableFields.size() - 1) {
                sql.append(MysqlGrammars.SYMBOL.COMMA);
            }
            primaryTableFieldIndex++;
        }
        if (hasJoinInfos) {
            sql.append(MysqlGrammars.SYMBOL.COMMA);
            Set<String> foreignAliasNames = joinInfos.keySet();
            for (String foreignAliasName : foreignAliasNames) {
                JoinInfo joinInfo = joinInfos.get(foreignAliasName);
                joinInfo.setForeignTableAliasName(foreignAliasName);
                List<QField> foreignFields = joinInfo.getForeignFields();
                if (foreignFields != null && !foreignFields.isEmpty()) {
                    int foreignFieldIndex = 0;
                    for (QField foreignField : foreignFields) {
                        String fieldType = foreignField.getFieldType();
                        if (fieldType != null && !fieldType.isEmpty()) {
                            FieldTypeCom fieldTypeCom = FieldTypeStore.get(fieldType);
                            if (fieldTypeCom != null && fieldTypeCom.isVirtual()) {
                                continue;
                            }
                        }
                        sql.append(foreignField.getSelectColumnName());
                        sql.append(MysqlGrammars.SYMBOL.BLANKET);
                        sql.append(MysqlGrammars.DQL.AS);
                        sql.append(MysqlGrammars.SYMBOL.BLANKET);
                        sql.append(MysqlGrammars.SYMBOL.S_ET_MARK).append(foreignField.getSelectColumnName()).append(MysqlGrammars.SYMBOL.S_ET_MARK);
                        if (foreignFieldIndex < foreignFields.size() - 1) {
                            sql.append(MysqlGrammars.SYMBOL.COMMA);
                        }
                        foreignFieldIndex++;
                    }
                }
            }
        }
        return sql.toString();
    }

    private String buildSelectFromTableSql(QTable primaryTable, Map<String, JoinInfo> joinInfos) {
        StringBuilder sql = new StringBuilder(MysqlGrammars.DQL.FROM).append(MysqlGrammars.SYMBOL.BLANKET).append(primaryTable.getTableName())
                .append(MysqlGrammars.SYMBOL.BLANKET);
        joinInfos = joinInfos == null? new HashMap<>() : joinInfos;
        boolean hasJoinInfos = !joinInfos.isEmpty();
        if (hasJoinInfos) {
            sql.append(MysqlGrammars.DQL.AS).append(MysqlGrammars.SYMBOL.BLANKET).append(QTableConstant.DQL.PRIMARY_TABLE).append(MysqlGrammars.SYMBOL.BLANKET);
            Set<String> foreignAliasNames = joinInfos.keySet();
            for (String foreignAliasName : foreignAliasNames) {
                JoinInfo joinInfo = joinInfos.get(foreignAliasName);
                joinInfo.setForeignTableAliasName(foreignAliasName);
                JoinInfo.JoinType joinType = joinInfo.getJoinType();
                QTable foreignTable = joinInfo.getForeignTable();
                String foreignTableAliasName = joinInfo.getForeignTableAliasName();
                List<JoinInfo.JoinField> joinFields = joinInfo.getJoinFields();
                switch (joinType) {
                    case INNER:
                        sql.append(MysqlGrammars.DQL.INNER_JOIN).append(MysqlGrammars.SYMBOL.BLANKET).append(foreignTable.getTableName())
                                .append(MysqlGrammars.SYMBOL.BLANKET).append(MysqlGrammars.DQL.AS).append(MysqlGrammars.SYMBOL.BLANKET).append(foreignTableAliasName);
                        break;
                    case LEFT:
                        sql.append(MysqlGrammars.DQL.LEFT_JOIN).append(MysqlGrammars.SYMBOL.BLANKET).append(foreignTable.getTableName())
                                .append(MysqlGrammars.SYMBOL.BLANKET).append(MysqlGrammars.DQL.AS).append(MysqlGrammars.SYMBOL.BLANKET).append(foreignTableAliasName);
                        break;
                    case RIGHT:
                        sql.append(MysqlGrammars.DQL.RIGHT_JOIN).append(MysqlGrammars.SYMBOL.BLANKET).append(foreignTable.getTableName())
                                .append(MysqlGrammars.SYMBOL.BLANKET).append(MysqlGrammars.DQL.AS).append(MysqlGrammars.SYMBOL.BLANKET).append(foreignTableAliasName);
                        break;
                    default:
                        sql.append(MysqlGrammars.DQL.JOIN).append(MysqlGrammars.SYMBOL.BLANKET).append(foreignTable.getTableName())
                            .append(MysqlGrammars.SYMBOL.BLANKET).append(MysqlGrammars.DQL.AS).append(MysqlGrammars.SYMBOL.BLANKET).append(foreignTableAliasName);
                        break;
                }
                if (joinFields != null && !joinFields.isEmpty()) {
                    sql.append(MysqlGrammars.SYMBOL.BLANKET).append(MysqlGrammars.DQL.ON);
                    int joinFieldIndex = 0;
                    for (JoinInfo.JoinField joinField : joinFields) {
                        QField primaryTableField = joinField.getPrimaryTableField();
                        QField foreignTableField = joinField.getForeignTableField();
                        if (joinFieldIndex != 0) {
                            sql.append(MysqlGrammars.SYMBOL.BLANKET).append(MysqlGrammars.CONDITION.AND);
                        }
                        sql.append(MysqlGrammars.SYMBOL.BLANKET).append(QTableConstant.DQL.PRIMARY_TABLE).append(MysqlGrammars.SYMBOL.COLON).append(primaryTableField.getFieldName());
                        sql.append(MysqlGrammars.SYMBOL.BLANKET).append(MysqlGrammars.CONDITION.EQ).append(foreignTableAliasName).append(MysqlGrammars.SYMBOL.COLON).append(foreignTableField.getFieldName());
                        joinFieldIndex++;
                    }
                } else {
                    throw new QDqlException("mysql dql language executes error : join fields is null.");
                }
            }
        }
        return sql.toString();
    }

    @Override
    public DatabaseSql selectListSql(QTable primaryTable, Map<String, JoinInfo> joinInfos, Map<String, JoinInfo> notJoinInfos,JSONObject param) {
        this.check(primaryTable);
        String columnSql = this.buildSelectColumnSql(primaryTable, joinInfos);
        String tableSql = this.buildSelectFromTableSql(primaryTable, joinInfos);
        String sql =
                MysqlGrammars.DQL.SELECT + MysqlGrammars.SYMBOL.BLANKET +
                        columnSql + MysqlGrammars.SYMBOL.BLANKET +
                tableSql + MysqlGrammars.SYMBOL.BLANKET;
        List<Object> params= new ArrayList<>();
        List<String> conditionSql = this.buildConditionSql(primaryTable, joinInfos, notJoinInfos, param, params);
        if (conditionSql != null && !conditionSql.isEmpty()) {
            StringBuilder conditionSqlBuilder = new StringBuilder();
            for (int i = 0; i < conditionSql.size(); i++) {
                conditionSqlBuilder.append(MysqlGrammars.SYMBOL.BLANKET);
                if (i != 0) {
                    conditionSqlBuilder.append(MysqlGrammars.CONDITION.AND).append(MysqlGrammars.SYMBOL.BLANKET);
                }
                conditionSqlBuilder.append(conditionSql.get(i));
            }
            sql += MysqlGrammars.CONDITION.WHERE + MysqlGrammars.SYMBOL.BLANKET + conditionSqlBuilder;
        }
        return new DatabaseSql(sql, params.toArray());
    }

    @Override
    public DatabaseSql selectListSql(QTable primaryTable, List<QCondition> conditions) {
        this.check(primaryTable);
        String columnSql = this.buildSelectColumnSql(primaryTable, null);
        String tableSql = this.buildSelectFromTableSql(primaryTable, null);
        String sql =
                MysqlGrammars.DQL.SELECT + MysqlGrammars.SYMBOL.BLANKET +
                        columnSql + MysqlGrammars.SYMBOL.BLANKET +
                        tableSql + MysqlGrammars.SYMBOL.BLANKET;
        List<Object> params= new ArrayList<>();
        List<String> conditionSql = this.buildConditionSql(primaryTable, conditions, params);
        if (conditionSql != null && !conditionSql.isEmpty()) {
            StringBuilder conditionSqlBuilder = new StringBuilder();
            for (int i = 0; i < conditionSql.size(); i++) {
                conditionSqlBuilder.append(MysqlGrammars.SYMBOL.BLANKET);
                if (i != 0) {
                    conditionSqlBuilder.append(MysqlGrammars.CONDITION.AND).append(MysqlGrammars.SYMBOL.BLANKET);
                }
                conditionSqlBuilder.append(conditionSql.get(i));
            }
            sql += MysqlGrammars.CONDITION.WHERE + MysqlGrammars.SYMBOL.BLANKET + conditionSqlBuilder;
        }
        return new DatabaseSql(sql, params.toArray());
    }

    //这里需要修改
    @Override
    public DatabaseSql selectPageSql(QTable table, JSONObject param, Integer pageNo, Integer pageSize) {
        return null;
    }

    @Override
    public DatabaseSql selectTotal(QTable table, JSONObject param) {
        return null;
    }

    @Override
    public DatabaseSql selectOneSql(QTable primaryTable, Map<String, JoinInfo> joinInfos, Object primaryValue) {
        this.check(primaryTable);
        String columnSql = this.buildSelectColumnSql(primaryTable, joinInfos);
        String tableSql = this.buildSelectFromTableSql(primaryTable, joinInfos);
        String sql =
                MysqlGrammars.DQL.SELECT + MysqlGrammars.SYMBOL.BLANKET +
                        columnSql + MysqlGrammars.SYMBOL.BLANKET +
                        tableSql + MysqlGrammars.SYMBOL.BLANKET;
        List<Object> params= new ArrayList<>();
        String tableType = primaryTable.getTableType();
        if (tableType == null || tableType.isEmpty()) {
            throw new QDqlException("mysql dql language executes error : table type is null.");
        }
        TableTypeCom tableTypeCom = TableTypeStore.get(tableType);
        if (tableTypeCom == null) {
            throw new QDqlException("mysql dql language executes error : table type is not found.");
        }
        QField primaryKeyField = tableTypeCom.getPrimaryKeyField();
        if (primaryKeyField == null) {
            throw new QDqlException("mysql dql language executes error : primary key field is null.");
        }
        String fieldName = primaryKeyField.getFieldName();
        if (joinInfos.isEmpty()) {
            sql += MysqlGrammars.CONDITION.WHERE + MysqlGrammars.SYMBOL.BLANKET + fieldName + MysqlGrammars.SYMBOL.BLANKET + MysqlGrammars.CONDITION.EQ +
                    MysqlGrammars.SYMBOL.BLANKET + MysqlGrammars.SYMBOL.VALUE_S;
        } else {
            sql += MysqlGrammars.CONDITION.WHERE + MysqlGrammars.SYMBOL.BLANKET + QTableConstant.DQL.PRIMARY_TABLE +
                    MysqlGrammars.SYMBOL.COLON + fieldName + MysqlGrammars.SYMBOL.BLANKET + MysqlGrammars.CONDITION.EQ +
                    MysqlGrammars.SYMBOL.BLANKET + MysqlGrammars.SYMBOL.VALUE_S;
        }
        params.add(primaryValue);
        return new DatabaseSql(sql, params.toArray());
    }

    //todo 修改改方法，适应新的查询逻辑
    @Override
    public List<String> buildConditionSql(QTable primaryTable, Map<String, JoinInfo> joinInfos, Map<String, JoinInfo> notJoinInfos, JSONObject param, List<Object> jdbcParams) {
        String tableType = primaryTable.getTableType();
        if (tableType == null || tableType.isEmpty()) {
            throw new QDqlException("mysql dql language executes error : table type is null.");
        }
        TableTypeCom tableTypeCom = TableTypeStore.get(tableType);
        List<String> rtn = new ArrayList<>();
        QField deleteKeyField = tableTypeCom.getDeleteKeyField();
        DeleteKey deleteKey = tableTypeCom.getDeleteKey();
        rtn.add((primaryTable.isNeedAs() ? primaryTable.getAsTable() + "." + deleteKeyField.getFieldName() : deleteKeyField.getFieldName()) + MysqlGrammars.SYMBOL.BLANKET
                + MysqlGrammars.CONDITION.EQ + MysqlGrammars.SYMBOL.BLANKET
                + MysqlGrammars.SYMBOL.VALUE_S);
        jdbcParams.add(deleteKey.normalFlag());
        if (!tableTypeCom.canBuildConditionSql()) {
            return rtn;
        }
        List<QField> tableFields = primaryTable.getFields();
        if (tableFields != null && !tableFields.isEmpty()) {
            for (QField tableField : tableFields) {
                boolean hasJoin = joinInfos != null && joinInfos.containsKey(tableField.getFieldName());
                String fieldType = tableField.getFieldType();
                if (fieldType == null || fieldType.isEmpty()) {
                    continue;
                }
                FieldTypeCom fieldTypeCom = FieldTypeStore.get(fieldType);
                if (fieldTypeCom == null) {
                    continue;
                }
                List<QCondition> conditions = fieldTypeCom.conditions(tableField, param);
                if (conditions != null && !conditions.isEmpty()) {
                    for (QCondition condition : conditions) {
                        String tablePrefix = "";
                        if (hasJoin) {
                            //如果该字段是有关联表的字段
                            QField conditionField = condition.getConditionField();
                            if (conditionField.getFieldName().equals(tableField.getFieldName()) &&
                                conditionField.getTableName().equals(tableField.getTableName()) &&
                                conditionField.getSourceName().equals(tableField.getSourceName()) &&
                                conditionField.getSchemeName().equals(tableField.getSchemeName())) {
                                //该查询项的字段是主表字段
                                tablePrefix = primaryTable.isNeedAs() ? QTableConstant.DQL.PRIMARY_TABLE + MysqlGrammars.SYMBOL.COLON : "";
                            } else {
                                //该查询项的字段是连接表字段
                                tablePrefix = tableField.getFieldName() + MysqlGrammars.SYMBOL.COLON;
                            }
                        } else {
                            //如果该字段不是关联表的字段
                            //todo 还要判断是否是跨库关联字段
                            if (notJoinInfos == null || !notJoinInfos.containsKey(tableField.getFieldName())) {
                                tablePrefix = primaryTable.isNeedAs() ? QTableConstant.DQL.PRIMARY_TABLE + MysqlGrammars.SYMBOL.COLON : "";
                            } else {
                                continue;
                            }
                        }
                        String conditionSql = "";
                        //如果该条件可以生成Sql语句
                        List<Object> values = condition.getValues();
                        if (values == null || values.isEmpty()) {
                            continue;
                        }
                        QCondition.ConditionType type = condition.getType();
                        switch (type) {
                            case EQ:
                                conditionSql = tablePrefix + condition.getConditionField().getFieldName() + MysqlGrammars.SYMBOL.BLANKET
                                        + MysqlGrammars.CONDITION.EQ + MysqlGrammars.SYMBOL.BLANKET
                                        + MysqlGrammars.SYMBOL.VALUE_S;
                                jdbcParams.add(values.get(0));
                                rtn.add(conditionSql);
                                break;
                            case RANGE:
                                conditionSql = tablePrefix + condition.getConditionField().getFieldName() + MysqlGrammars.SYMBOL.BLANKET
                                        + MysqlGrammars.CONDITION.GE + MysqlGrammars.SYMBOL.BLANKET
                                        + MysqlGrammars.SYMBOL.VALUE_S + MysqlGrammars.SYMBOL.BLANKET;
                                jdbcParams.add(values.get(0));
                                if (values.size() > 1) {
                                    conditionSql = conditionSql + MysqlGrammars.CONDITION.AND
                                            + MysqlGrammars.SYMBOL.BLANKET + tablePrefix + condition.getConditionField().getFieldName() + MysqlGrammars.SYMBOL.BLANKET
                                            + MysqlGrammars.CONDITION.LE + MysqlGrammars.SYMBOL.BLANKET
                                            + MysqlGrammars.SYMBOL.VALUE_S + MysqlGrammars.SYMBOL.BRACKET_RIGHT;
                                    jdbcParams.add(values.get(1));
                                }
                                rtn.add(conditionSql);
                                break;
                            case LIKE:
                                conditionSql = tablePrefix + condition.getConditionField().getFieldName() + MysqlGrammars.SYMBOL.BLANKET
                                        + MysqlGrammars.CONDITION.LK + MysqlGrammars.SYMBOL.BLANKET
                                        + MysqlGrammars.SYMBOL.VALUE_S;
                                jdbcParams.add("%" + values.get(0).toString() + "%");
                                rtn.add(conditionSql);
                                break;
                            default:
                                break;
                        }
                    }
                }
            }
        }
        return rtn;
    }

    @Override
    public List<String> buildConditionSql(QTable table, List<QCondition> conditions,  List<Object> jdbcParams) {
        List<String> rtn = new ArrayList<>();
        if (conditions != null && !conditions.isEmpty()) {
            for (QCondition condition : conditions) {
                String tablePrefix = "";
                String conditionSql;
                //如果该条件可以生成Sql语句
                List<Object> values = condition.getValues();
                if (values == null || values.isEmpty()) {
                    continue;
                }
                QCondition.ConditionType type = condition.getType();
                switch (type) {
                    case EQ:
                        conditionSql = tablePrefix + condition.getConditionField().getFieldName() + MysqlGrammars.SYMBOL.BLANKET
                                + MysqlGrammars.CONDITION.EQ + MysqlGrammars.SYMBOL.BLANKET
                                + MysqlGrammars.SYMBOL.VALUE_S;
                        jdbcParams.add(values.get(0));
                        rtn.add(conditionSql);
                        break;
                    case RANGE:
                        conditionSql = tablePrefix + condition.getConditionField().getFieldName() + MysqlGrammars.SYMBOL.BLANKET
                                + MysqlGrammars.CONDITION.GE + MysqlGrammars.SYMBOL.BLANKET
                                + MysqlGrammars.SYMBOL.VALUE_S + MysqlGrammars.SYMBOL.BLANKET;
                        jdbcParams.add(values.get(0));
                        if (values.size() > 1) {
                            conditionSql = conditionSql + MysqlGrammars.CONDITION.AND
                                    + MysqlGrammars.SYMBOL.BLANKET + tablePrefix + condition.getConditionField().getFieldName() + MysqlGrammars.SYMBOL.BLANKET
                                    + MysqlGrammars.CONDITION.LE + MysqlGrammars.SYMBOL.BLANKET
                                    + MysqlGrammars.SYMBOL.VALUE_S + MysqlGrammars.SYMBOL.BRACKET_RIGHT;
                            jdbcParams.add(values.get(1));
                        }
                        rtn.add(conditionSql);
                        break;
                    case LIKE:
                        conditionSql = tablePrefix + condition.getConditionField().getFieldName() + MysqlGrammars.SYMBOL.BLANKET
                                + MysqlGrammars.CONDITION.LK + MysqlGrammars.SYMBOL.BLANKET
                                + MysqlGrammars.SYMBOL.VALUE_S;
                        jdbcParams.add("%" + values.get(0).toString() + "%");
                        rtn.add(conditionSql);
                        break;
                    default:
                        break;
                }
            }
        }
        return rtn;
    }

}
